# coding: utf-8
import MySQLdb
import MySQLdb.cursors
from warnings import filterwarnings
filterwarnings('ignore', category = MySQLdb.Warning)

import time

"""
MySQLdbUtil module.

Created on 12/29/2017

@author: 强哥
"""

MYSQL_ERROR_FORMAT = 'MysqlError %s :%s'
MYSQL_CLOSE_INFO = "MySQL Connection Close ..."
MYSQL_CONNECTION_SUCCESS_INFO = "MySQL Connection Success ..."
SQL_FORMAT = "%s INTO %s %s  VALUES %s %s;"

def safe(text):
	"""

	字符串转义
	"""
	return MySQLdb.escape_string(text)

def connect(dbInfo, selectType=False):
	"""

	创建数据库连接实例
	"""	
	dbHost 	= dbInfo.get('MYSQL_HOST', '127.0.0.1')
	dbUser 	= dbInfo.get('MYSQL_USER', 'root')
	dbPasswd = dbInfo.get('MYSQL_PASSWD', 'root')
	dbPort 	= dbInfo.get('MYSQL_PORT', 3306)
	dbName 	= dbInfo.get('MYSQL_DBNAME', 'test')
	cursorclass = MySQLdb.cursors.DictCursor 
	
	if selectType == "dict":
		cursorclass = MySQLdb.cursors.DictCursor 
	else:
		cursorclass = MySQLdb.cursors.Cursor
	
	try:
		connect = MySQLdb.connect(host 	= dbHost,
								user 	= dbUser, 
								passwd 	= dbPasswd, 
								charset = 'utf8', 
								port 	= dbPort,
								db 		= dbName,
								cursorclass = cursorclass
								)
		print MYSQL_CONNECTION_SUCCESS_INFO
		return connect
	except MySQLdb.Error as error:
		printError(error)
		return False

def execSql(connect, sql):
	"""

	执行sql
	"""
	status = 0
	cursor = connect.cursor()
	try:
		status = cursor.execute(sql)
		connect.commit()
	except MySQLdb.Error as error:
		connect.rollback()
		printError(error)
	cursor.close()
	return status

def select(connect, sql):
	"""

	查询sql, 并返回结果
	"""
	datas = None
	try:
		cursor = connect.cursor()
		cursor.execute(sql)
		datas = cursor.fetchall()
		cursor.close()
	except MySQLdb.Error as error:
		printError(error)
	return datas

def printError(error):
	print MYSQL_ERROR_FORMAT % (error[0], error[1])


class GetInsertSql(object):
	"""

	自动生成, 插入语句
	"""
	def __init__(self):
		self.__dataInfo = ''
		self.__fieldNames = ''
		self.__values = ''
		self.__sql = ''
		self.__updateFields = ''
		self.__tableName = ''
		self.__insertType = ''

	def sql(self, dataInfo, tableName, insertType='ignore'):
		"""

		返回sql
		"""
		self.__setData(dataInfo, tableName, insertType)
		self.__dataToInsertSql()
		return self.__sql

	def __setData(self, dataInfo, tableName, insertType):
		"""

		设置数据类型
		"""
		self.__tableName = tableName
		self.__insertType = insertType
		if isinstance(dataInfo, list):
			self.__dataInfo = dataInfo
		elif isinstance(dataInfo, dict):
			self.__dataInfo = [dataInfo]

	def __setFieldNames(self, item):
		"""

		获取字段名字符串
		"""
		fieldNames = []
		for k in item.keys():
			if isinstance(k, unicode):
				fieldNames.append(k.encode('utf-8'))
			else:
				fieldNames.append(k)

		self.__fieldNames = "(%s)" % ', '.join(["`%s`" % key for key in fieldNames])

	def __setValues(self, items):
		"""

		获取字段值字符串
		"""
		tmplists = []
		for item in items:
			tmplist = []
			for val in item.values():
				if isinstance(val, unicode):
					val = val.encode('utf-8', "ignore")
				
				try:
					tmp = "%s" % safe(str(val))
				except Exception as e:
					logging.log(logging.INFO, 'setValues Faild: %s' % e)
					return

				tmp = 'NULL' if tmp == 'None' else "'%s'" % tmp
				tmplist.append(tmp)
			tmplists.append(tmplist)

		self.__values = ", ".join(['(%s)' % ', '.join(tmplist) for tmplist in tmplists])

	def __getUpdateFields(self, item):
		"""

		插入或更新
		"""
		self.__updateFields = 'ON DUPLICATE KEY UPDATE %s' % ','.join(['`%s` = VALUES(`%s`)' % (k, k) for k in item.keys()])

	def __dataToInsertSql(self):
		"""

		list 转 sql
		"""
		self.__setFieldNames(self.__dataInfo[0])
		self.__setValues(self.__dataInfo)
		if self.__insertType == 'update':
			self.__getUpdateFields(self.__dataInfo[0])
		self.__getInsertSql()

	def __getInsertSql(self, dataType='list'):
		"""

		根据插入类型生成相应sql
		"""
		if self.__insertType == 'ignore':
			self.__sql = SQL_FORMAT % ("INSERT IGNORE", self.__tableName, self.__fieldNames, self.__values, '')
		elif self.__insertType == 'replace':
			self.__sql = SQL_FORMAT % ("REPLACE", self.__tableName, self.__fieldNames, self.__values, '')
		elif self.__insertType == 'update':
			self.__sql = SQL_FORMAT % ("INSERT IGNORE", self.__tableName, self.__fieldNames, self.__values, self.__updateFields)
		else:
			pass

def example():
	"""

	例子
	"""
	dataInfo = [{'a':'b'}]
	# dataInfo = {'id': 1, 'name':'老子2'}
	insertTypes = ['ignore', 'replace', 'update']
	for i in insertTypes:
		sql = getInsertSql.sql(dataInfo, 'test_test', i)
		print sql

getInsertSql = GetInsertSql()

if __name__ == '__main__':
	example()